﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class ApprovalTaskDal
    {
        private readonly DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);
        private readonly Common _common = new Common();

        #region Select

        public List<approval_task> GetApprovalTask(approval_task filter, bool includeCompleted = false,bool isGetRuleApproveOfStep = true)
        {
            var results = new List<approval_task>();
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(9);
                dbManager.AddParameters(i++, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@approval_task_seq", filter.ApprovalTaskSeq);
                dbManager.AddParameters(i++, "@title", filter.Title);
                dbManager.AddParameters(i++, "@task_employee_creator", filter.RequestEmpSeq);
                dbManager.AddParameters(i++, "@start_when", filter.StartWhen);
                dbManager.AddParameters(i++, "@stop_when", filter.StopWhen);
                dbManager.AddParameters(i++, "@task_status", filter.Status);
                dbManager.AddParameters(i++, "@ref_seq", filter.RefSeq);
                dbManager.AddParameters(i++, "@rule_approve_type_seq", filter.Reference != null ? filter.Reference.rule_approve_type_seq : 0);

                var strSQL = new StringBuilder(@"                        
                         select a.approval_task_seq
                          , a.wf_instant_id
                          , a.rule_approve_seq
                          , a.title
                          , a.start_when
                          , a.stop_when
                          , a.task_status
                          , a.creator
                          , a.current_approvers
                          , a.current_step
                          , a.history
                          , a.task_status_complete
                          , a.record_status
                          , a.created_by
                          , a.created_when
                          , a.updated_by
                          , a.updated_when
                          , a.ref_seq
                          , a.ref_type_seq
                            , a.action_count
                            , t.rule_approve_type_seq
                            , t.rule_approve_group_seq
                        From approval_task a
                            LEFT JOIN rule_approve r ON a.rule_approve_seq = r.rule_approve_seq AND r.record_status <> @delete_status
                            LEFT JOIN rule_approve_type t ON t.rule_approve_type_seq = r.rule_approve_type_seq AND r.record_status <> @delete_status
                        WHERE a.record_status <> @delete_status 
                    ");
                strSQL.Append(filter.ApprovalTaskSeq > 0 ? " and a.approval_task_seq = @approval_task_seq" : String.Empty);
                strSQL.Append(!String.IsNullOrWhiteSpace(filter.Title) ? " and a.title = @title" : String.Empty);
                strSQL.Append(filter.StopWhen != null ? " and a.stop_when = @stop_when" : String.Empty);
                strSQL.Append(filter.Status != TaskStatus.Initializing ? " and a.task_status = @task_status" : String.Empty);
                strSQL.Append(!includeCompleted ? " and a.task_status_complete <> true" : String.Empty);
                strSQL.Append(filter.RefSeq != 0 && filter.Reference != null ? " and a.ref_seq = @ref_seq and t.rule_approve_type_seq = @rule_approve_type_seq" : String.Empty);

                var strSQL2 = strSQL.ToString();
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL2).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    var reference = _common.ConvertToNullableInt(dr["ref_seq"]).HasValue
                                        ? new approval_reference
                                            {
                                                ref_seq = Convert.ToInt32(dr["ref_seq"]),
                                                rule_approve_type_seq = Convert.ToInt32(dr["ref_type_seq"]),
                                                rule_approve_group_seq = dr["rule_approve_group_seq"] != DBNull.Value ? Convert.ToInt32(dr["rule_approve_group_seq"]) : 0
                                            }
                                        : null;

                    var result = new approval_task
                        {
                            ApprovalTaskSeq = Convert.ToInt32(dr["approval_task_seq"] != DBNull.Value ? Convert.ToInt32(dr["approval_task_seq"]) : 0),
                            WfInstantId = Guid.Parse((string) dr["wf_instant_id"]),
                            Title = Convert.ToString(dr["title"]),
                            StartWhen = Convert.ToDateTime(dr["start_when"]),
                            StopWhen = _common.ConvertToNullableDateTime(dr["stop_when"]),
                            Status = ApprovalUtil.TaskStatusFromString(Convert.ToString(dr["task_status"])),
                            RequestEmpSeq = Convert.ToInt32(dr["creator"]),
                            CurrentStep = _common.ConvertToNullableInt(dr["current_step"]) ?? -1,
                            Reference = reference
                        };

                    bool isHeIsTop;
                    if (isGetRuleApproveOfStep)
                    {
                        result.CurrentRuleApprove = RuleApproveDb.GetRuleApproveOfStep(result.ApprovalTaskSeq, result.CurrentStep
                                                                                       , GetPosSeqFromEmpSeq(result.RequestEmpSeq), out isHeIsTop);
                    }
                    results.Add(result);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            return results;
        }

        public static int GetPosSeqFromEmpSeq(int? emp_seq)
        {
            if (!emp_seq.HasValue) return 0;

            var employeeDal = new Employee();
            var employeeList = employeeDal.GetHrmPsEmployeeLite();
            var employeeSpecified = employeeList.FirstOrDefault(o => o.emp_seq == emp_seq);
            if (employeeSpecified != null && employeeSpecified.pos_seq != null)
                return employeeSpecified.pos_seq.Value;

            return 0;
        }

        public approval_reference GetApprovalReference(int refTypeSeq)
        {
            var approvalReference = new approval_reference();
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(2);
                dbManager.AddParameters(i++, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@refTypeSeq", refTypeSeq);

                string strSql = @"
SELECT t.rule_approve_type_seq, 
    g.rule_approve_group_seq, 
    g.ref_action_name, 
    g.ref_controller_name, 
    t.rule_approve_type_name, 
    g.rule_approve_group_name, 
    g.ref_to_table_name, 
    g.ref_column_seq_name 
FROM rule_approve_type t
    INNER JOIN rule_approve_group g ON t.rule_approve_group_seq = g.rule_approve_group_seq
WHERE t.rule_approve_type_seq = @refTypeSeq AND
    t.record_status <> @delete_status AND
    g.record_status <> @delete_status";

                DataRowCollection drc = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    approvalReference.column_seq_name = Convert.ToString(drc[0]["ref_column_seq_name"]);
                    approvalReference.ref_to_table_name = Convert.ToString(drc[0]["ref_to_table_name"]);
                    approvalReference.ref_action_name = Convert.ToString(drc[0]["ref_action_name"]);
                    approvalReference.ref_controller_name = Convert.ToString(drc[0]["ref_controller_name"]);
                    approvalReference.rule_approve_group_name = Convert.ToString(drc[0]["rule_approve_group_name"]);
                    approvalReference.rule_approve_type_name = Convert.ToString(drc[0]["rule_approve_type_name"]);
                    approvalReference.rule_approve_group_seq = Convert.ToInt32(drc[0]["rule_approve_group_seq"]);
                    approvalReference.rule_approve_type_seq = refTypeSeq;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            return approvalReference;
        }

        #endregion

        #region Insert

        public approval_task InsertApprovalTask(approval_task data)
        {
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(18);
                dbManager.AddParameters(i++, "@record_status", Common.UsedStatus);
                dbManager.AddParameters(i++, "@created_by", data.CreatedBy);
                dbManager.AddParameters(i++, "@created_when", data.CreatedWhen);
                dbManager.AddParameters(i++, "@updated_by", data.UpdatedBy);
                dbManager.AddParameters(i++, "@updated_when", data.UpdatedWhen);
                dbManager.AddParameters(i++, "@rule_approve_seq", data.CurrentRuleApprove == null ? (data.CurrentStep == 0 && data.RuleApproves.Count > 0 ? data.RuleApproves[0].rule_approve_seq : (int?) null) : data.CurrentRuleApprove.rule_approve_seq);
                dbManager.AddParameters(i++, "@title", data.Title);
                dbManager.AddParameters(i++, "@creator", data.RequestEmpSeq);
                dbManager.AddParameters(i++, "@current_approvers", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.approvers_seq_string);
                dbManager.AddParameters(i++, "@current_step", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.step);
                dbManager.AddParameters(i++, "@history", data.AllHistory());
                dbManager.AddParameters(i++, "@start_when", data.StartWhen);
                dbManager.AddParameters(i++, "@stop_when", data.StopWhen);
                dbManager.AddParameters(i++, "@task_status", data.Status);
                dbManager.AddParameters(i++, "@wf_instant_id", data.WfInstantId);
                dbManager.AddParameters(i++, "@task_status_complete", ApprovalUtil.IsComplete(data.Status));
                dbManager.AddParameters(i++, "@ref_seq", data.RefSeq);
                dbManager.AddParameters(i++, "@ref_type_seq", data.RefTypeSeq);

                var strSQL = new StringBuilder(@"
                        INSERT INTO approval_task
                                    ( rule_approve_seq
                                     , title
                                     , wf_instant_id
                                     , creator
                                     , current_approvers
                                     , current_step
                                     , history
                                     , start_when
                                     , stop_when
                                     , task_status
                                     , task_status_complete
                                     , record_status
                                     , created_by
                                     , created_when
                                     , updated_by
                                     , updated_when
                                     , ref_seq
                                     , ref_type_seq)
                        VALUES (  @rule_approve_seq
                                , @title
                                , @wf_instant_id
                                , @creator
                                , @current_approvers
                                , @current_step
                                , @history
                                , @start_when
                                , @stop_when
                                , @task_status
                                , @task_status_complete
                                , @record_status
                                , @created_by
                                , @created_when
                                , @updated_by
                                , @updated_when
                                , @ref_seq
                                , @ref_type_seq);
                    ");

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL.ToString());

                // Get New Seq Inserted
                var strSqlLastInsert = new StringBuilder("select distinct last_insert_id() FROM approval_task");
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSqlLastInsert.ToString());
                dbManager.CommitTransaction();
                data.ApprovalTaskSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            return data;
        }

        #endregion

        #region Update

        public void UpdateApprovalTask(approval_task data)
        {
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(18);
                dbManager.AddParameters(i++, "@record_status", Common.UsedStatus);
                dbManager.AddParameters(i++, "@approval_task_seq", data.ApprovalTaskSeq);
                dbManager.AddParameters(i++, "@created_by", data.CreatedBy);
                dbManager.AddParameters(i++, "@created_when", data.CreatedWhen);
                dbManager.AddParameters(i++, "@updated_by", data.UpdatedBy);
                dbManager.AddParameters(i++, "@updated_when", data.UpdatedWhen);
                dbManager.AddParameters(i++, "@rule_approve_seq", data.CurrentRuleApprove == null ? (data.CurrentStep == 0 && data.RuleApproves.Count > 0 ? data.RuleApproves[0].rule_approve_seq : (int?) null) : data.CurrentRuleApprove.rule_approve_seq);
                dbManager.AddParameters(i++, "@title", data.Title);
                dbManager.AddParameters(i++, "@creator", data.RequestEmpSeq);
                dbManager.AddParameters(i++, "@current_approvers", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.approvers_seq_string);
                dbManager.AddParameters(i++, "@current_step", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.step);
                dbManager.AddParameters(i++, "@history", data.AllHistory());
                dbManager.AddParameters(i++, "@start_when", data.StartWhen);
                dbManager.AddParameters(i++, "@stop_when", data.StopWhen);
                dbManager.AddParameters(i++, "@task_status", data.Status.ToString());
                dbManager.AddParameters(i++, "@task_status_complete", ApprovalUtil.IsComplete(data.Status));
                dbManager.AddParameters(i++, "@ref_seq", data.RefSeq);
                dbManager.AddParameters(i++, "@ref_type_seq", data.RefTypeSeq);

                var strSql = new StringBuilder(@"
                         UPDATE approval_task
                            SET rule_approve_seq = @rule_approve_seq
                              , title = @title
                              , creator = @creator
                              , current_approvers = @current_approvers
                              , current_step = @current_step
                              , history = @history
                              , start_when = @start_when
                              , stop_when = @stop_when
                              , task_status = @task_status
                              , task_status_complete = @task_status_complete
                              , record_status = @record_status
                              , created_by = @created_by
                              , created_when = @created_when
                              , updated_by = @updated_by
                              , updated_when = @updated_when
                              , ref_seq = @ref_seq
                              , ref_type_seq = @ref_type_seq
                            WHERE approval_task_seq = @approval_task_seq;
                    ");

                dbManager.ExecuteNonQuery(CommandType.Text, strSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }


        public void UpdateApprovalTaskHistory(int? approval_task_seq, int? emp_seq, string status_on_this_step, string status_on_priv_step,
            string remarks, string message_title, string message_reasons, List<int> messages_seq, string action)
        {
            /* this method is to insert histories data of each step of approving, such as change approval_task status from, to xxx*/

            var strSql = "";
            try
            {
                dbManager.Open();

                /* write new history */
                int i = 0;
                dbManager.CreateParameters(10);
                dbManager.AddParameters(i++, "@approval_task_seq", approval_task_seq);
                dbManager.AddParameters(i++, "@emp_seq", emp_seq);
                dbManager.AddParameters(i++, "@approval_task_history_status_on_this_step", status_on_this_step);

                dbManager.AddParameters(i++, "@approval_task_history_status_on_priv_step", status_on_priv_step);
                dbManager.AddParameters(i++, "@remarks", remarks);
                dbManager.AddParameters(i++, "@message_title", message_title);

                dbManager.AddParameters(i++, "@message_reasons", message_reasons);
                dbManager.AddParameters(i++, "@action", action);
                dbManager.AddParameters(i++, "@message_seq", null);
                dbManager.AddParameters(i++, "@approval_task_history_seq", null);

                strSql = @"
INSERT INTO approval_task_history
            (approval_task_seq,
             emp_seq,
             approval_task_history_date_action,
             approval_task_history_status_on_this_step,
             approval_task_history_status_on_priv_step,
             remarks,
             message_title,
             message_reasons,
             action
   ) VALUES (@approval_task_seq,
            @emp_seq,
            NOW(),
            @approval_task_history_status_on_this_step,
            @approval_task_history_status_on_priv_step,
            @remarks,
            @message_title,
            @message_reasons,
            @action);
                    ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSql);

                strSql = "SELECT MAX(approval_task_history_seq) FROM approval_task_history";
                var dataTableLastSeq = dbManager.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
                int? lastHistorySeq = dataTableLastSeq.Rows.Count > 0 &&
                                      dataTableLastSeq.Rows[0][0] != null &&
                                      dataTableLastSeq.Rows[0][0] != DBNull.Value
                                          ? Convert.ToInt32(dataTableLastSeq.Rows[0][0])
                                          : (int?) null;

                if (lastHistorySeq.HasValue)
                {
                    foreach (int i1 in messages_seq)
                    {
                        dbManager.AddParameters(i - 2, "@message_seq", i1);
                        dbManager.AddParameters(i - 1, "@approval_task_history_seq", lastHistorySeq.Value);
                        strSql = "UPDATE message SET approval_task_history_seq = @approval_task_history_seq WHERE message_seq = @message_seq";
                        dbManager.ExecuteNonQuery(CommandType.Text, strSql);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void UpdateOrAddIfNotExistApprovalTask(approval_task data)
        {
            bool succeed = false;
            try
            {
                dbManager.Open();

                int i = 0;
                dbManager.CreateParameters(18);
                dbManager.AddParameters(i++, "@record_status", Common.UsedStatus);
                dbManager.AddParameters(i++, "@created_by", data.CreatedBy);
                dbManager.AddParameters(i++, "@created_when", data.CreatedWhen);
                dbManager.AddParameters(i++, "@updated_by", data.UpdatedBy);
                dbManager.AddParameters(i++, "@updated_when", data.UpdatedWhen);
                dbManager.AddParameters(i++, "@rule_approve_seq", data.CurrentRuleApprove == null ? (int?) null : data.CurrentRuleApprove.rule_approve_seq);
                dbManager.AddParameters(i++, "@title", data.Title);
                dbManager.AddParameters(i++, "@creator", data.RequestEmpSeq);
                dbManager.AddParameters(i++, "@current_approvers", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.approvers_seq_string);
                dbManager.AddParameters(i++, "@current_step", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.step);
                dbManager.AddParameters(i++, "@history", data.AllHistory());
                dbManager.AddParameters(i++, "@start_when", data.StartWhen);
                dbManager.AddParameters(i++, "@stop_when", data.StopWhen);
                dbManager.AddParameters(i++, "@task_status", data.Status);
                dbManager.AddParameters(i++, "@wf_instant_id", data.WfInstantId);
                dbManager.AddParameters(i++, "@task_status_complete", ApprovalUtil.IsComplete(data.Status));
                dbManager.AddParameters(i++, "@ref_seq", data.RefSeq);
                dbManager.AddParameters(i++, "@ref_type_seq", data.RefTypeSeq);

                var strSQL = new StringBuilder(@"
                        INSERT INTO approval_task
                                    ( rule_approve_seq
                                     , title
                                     , wf_instant_id
                                     , creator
                                     , current_approvers
                                     , current_step
                                     , history
                                     , start_when
                                     , stop_when
                                     , task_status
                                     , task_status_complete
                                     , record_status
                                     , created_by
                                     , created_when
                                     , updated_by
                                     , updated_when
                                     , ref_seq
                                     , ref_type_seq)
                        VALUES (  @rule_approve_seq
                                , @title
                                , @wf_instant_id
                                , @creator
                                , @current_approvers
                                , @current_step
                                , @history
                                , @start_when
                                , @stop_when
                                , @task_status
                                , @task_status_complete
                                , @record_status
                                , @created_by
                                , @created_when
                                , @updated_by
                                , @updated_when
                                , @ref_seq
                                , @ref_type_seq);
                    ");

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL.ToString());
                succeed = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }

            if (succeed) return;

            try
            {

                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(18);
                dbManager.AddParameters(i++, "@record_status", Common.UsedStatus);
                dbManager.AddParameters(i++, "@approval_task_seq", data.ApprovalTaskSeq);
                dbManager.AddParameters(i++, "@created_by", data.CreatedBy);
                dbManager.AddParameters(i++, "@created_when", data.CreatedWhen);
                dbManager.AddParameters(i++, "@updated_by", data.UpdatedBy);
                dbManager.AddParameters(i++, "@updated_when", data.UpdatedWhen);
                dbManager.AddParameters(i++, "@rule_approve_seq", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.rule_approve_seq.ToString());
                dbManager.AddParameters(i++, "@title", data.Title);
                dbManager.AddParameters(i++, "@creator", data.RequestEmpSeq);
                dbManager.AddParameters(i++, "@current_approvers", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.approvers_seq_string);
                dbManager.AddParameters(i++, "@current_step", data.CurrentRuleApprove == null ? null : data.CurrentRuleApprove.step);
                dbManager.AddParameters(i++, "@history", data.AllHistory());
                dbManager.AddParameters(i++, "@start_when", data.StartWhen);
                dbManager.AddParameters(i++, "@stop_when", data.StopWhen);
                dbManager.AddParameters(i++, "@task_status", data.Status.ToString());
                dbManager.AddParameters(i++, "@task_status_complete", ApprovalUtil.IsComplete(data.Status));
                dbManager.AddParameters(i++, "@ref_seq", data.RefSeq);
                dbManager.AddParameters(i++, "@ref_type_seq", data.RefTypeSeq);

                var strSql = new StringBuilder(@"
                         UPDATE approval_task
                            SET rule_approve_seq = @rule_approve_seq
                              , title = @title
                              , creator = @creator
                              , current_approvers = @current_approvers
                              , current_step = @current_step
                              , history = @history
                              , start_when = @start_when
                              , stop_when = @stop_when
                              , task_status = @task_status
                              , task_status_complete = @task_status_complete
                              , record_status = @record_status
                              , created_by = @created_by
                              , created_when = @created_when
                              , updated_by = @updated_by
                              , updated_when = @updated_when
                              , ref_seq = @ref_seq
                              , ref_type_seq = @ref_type_seq
                            WHERE approval_task_seq = @approval_task_seq;
                    ");

                dbManager.ExecuteNonQuery(CommandType.Text, strSql.ToString());
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void UpdateApprovalTaskStatus(int? pApprovalTaskSeq, string pWfInstantId, TaskStatus pTaskStatus)
        {
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(5);
                dbManager.AddParameters(i++, "@record_status", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@approval_task_seq", pApprovalTaskSeq);
                dbManager.AddParameters(i++, "@task_status", pTaskStatus.ToString());
                dbManager.AddParameters(i++, "@task_status_complete", ApprovalUtil.IsComplete(pTaskStatus));
                dbManager.AddParameters(i++, "@wf_instant_id", pWfInstantId);

                var strSQL = new StringBuilder(@"
                         UPDATE approval_task
                            SET task_status = @task_status
                              , task_status_complete = @task_status_complete 
                            WHERE false record_status <> @record_status AND (false ");

                if (pApprovalTaskSeq.HasValue) strSQL.Append(" OR approval_task_seq = @approval_task_seq ");
                if (pApprovalTaskSeq.HasValue) strSQL.Append(" OR wf_instant_id = @wf_instant_id ");

                strSQL.Append(")");

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        public void UpdateRecordStatusInRefTable(int refSeq, int refTypeSeq, string newRecordStatus, int updateBy)
        {
            dbManager.Open();
            DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, @"SELECT t.rule_approve_type_seq, g.rule_approve_group_seq,
g.ref_to_database, g.ref_to_table_name, g.ref_column_seq_name, g.ref_action_name, g.ref_controller_name
 FROM rule_approve_type t INNER JOIN rule_approve_group g ON t.rule_approve_group_seq = g.rule_approve_group_seq 
where rule_approve_type_seq = " + refTypeSeq).Tables[0];

            string rule_approve_type_seq = "";
            string rule_approve_group_seq = "";
            string ref_to_database = "";
            string ref_to_table_name = "";
            string ref_column_seq_name = "";
            string ref_action_name = "";
            string ref_controller_name = "";

            if (dt.Rows.Count > 0)
            {
                rule_approve_type_seq = Convert.ToString(dt.Rows[0]["rule_approve_type_seq"]);
                rule_approve_group_seq = Convert.ToString(dt.Rows[0]["rule_approve_group_seq"]);
                ref_to_database = Convert.ToString(dt.Rows[0]["ref_to_database"]);
                ref_to_table_name = Convert.ToString(dt.Rows[0]["ref_to_table_name"]);
                ref_column_seq_name = Convert.ToString(dt.Rows[0]["ref_column_seq_name"]);
                ref_action_name = Convert.ToString(dt.Rows[0]["ref_action_name"]);
                ref_controller_name = Convert.ToString(dt.Rows[0]["ref_controller_name"]);
            }
            dbManager.Close();

            if (ref_to_database == "SqlServer")
            {
                var conString = ConfigurationSettings.AppSettings["SqlServer"];
                var conn = new SqlConnection(conString);
                
                conn.Open();

                // create a SqlCommand object for this connection
                SqlCommand command = conn.CreateCommand();
                command.CommandText =  string.Format(@"UPDATE {0}
                                                SET RecordStatus = '{2}',
                                                  UpdatedBy = '{3}',
                                                  UpdatedWhen = GetDate()
                                                WHERE {1} = {4};",
                                               ref_to_table_name, 
                                               ref_column_seq_name,
                                               newRecordStatus,
                                               updateBy,
                                               refSeq
                                               ); 
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery(); 
                conn.Close();
            }
            else
            {

                var reference = GetApprovalReference(refTypeSeq);

                try
                {
                    dbManager.Open();
                    int i = 0;
                    dbManager.CreateParameters(4);
                    dbManager.AddParameters(i++, "@delete_status", Common.DeletedStatus);
                    dbManager.AddParameters(i++, "@seq", refSeq);
                    dbManager.AddParameters(i++, "@updateBy", updateBy);
                    dbManager.AddParameters(i++, "@newRecordStatus", newRecordStatus);

                    var strSql = string.Format(@"UPDATE `{0}`
                                                SET `record_status` = @newRecordStatus,
                                                  `updated_by` = @updateBy,
                                                  `updated_when` = NOW()
                                                WHERE `{1}` = @seq;",
                                               reference.ref_to_table_name, reference.column_seq_name);

                    dbManager.ExecuteNonQuery(CommandType.Text, strSql);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (dbManager.Connection != null)
                    {
                        dbManager.Connection.Close();
                        dbManager.Dispose();
                    }
                }
            }
        }

        #endregion

        #region Delete

        public void DeleteApprovalTask(int approvalTaskSeq)
        {
            try
            {
                dbManager.Open();
                int i = 0;
                dbManager.CreateParameters(3);
                dbManager.AddParameters(i++, "@record_delete_status", Common.DeletedStatus);
                dbManager.AddParameters(i++, "@record_used_status", Common.UsedStatus);
                dbManager.AddParameters(i++, "@approval_task_seq", approvalTaskSeq);

                var strSQL = new StringBuilder(@"
                          UPDATE approval_task
                            SET record_status = @record_delete_status 
                            WHERE approval_task_seq = @approval_task_seq
                                AND record_status = @record_delete_status;
                    ");

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Connection.Close();
                    dbManager.Dispose();
                }
            }
        }

        #endregion
    }
}